package com.roc.dao.impl;

import java.util.LinkedList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.hibernate.NonUniqueResultException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.roc.dao.DaoException;
import com.roc.dao.profit.IProfitDao;
import com.roc.pojo.Operators;
import com.roc.pojo.ProfitRule;
import com.roc.util.constant.Role;

@Repository
public class ProfitDaoImpl implements IProfitDao {
	
	static Logger logger = LogManager.getLogger(BaseDaoImpl.class);
	@Resource(name = "sessionFactoryDg")
	SessionFactory sessionFactoryDg;

	//查询云商宝代理商
	@Override
	public List getYsbOpertorsIdsTreeById(Long id,String roleType) throws DaoException {
		Session session=null;
		try {
			 session = sessionFactoryDg.openSession();
			 String sql=null;
			 if(id!=null){
			     if(roleType.equals(Role.SYSTEM.getCode())){
			            sql="select a.agent_id id, (select o.real_name from apsmgm.t_operators o where o.id = a.agent_id) real_name "
						    +"from apsmgm.t_pos_agent a group by a.agent_id"; 
			     }else{
			    	    sql= "select a.agent_id id, b.real_name"
			    			+" from apsmgm.t_pos_agent a, (select o.id, o.real_name from apsmgm.t_operators o"
		                    +" where o.role_type in ('02', '03')  START WITH id ="+id
		                    +" CONNECT BY supri_id = PRIOR id) b"
		                    +" where  a.agent_id = b.id  group by a.agent_id, b.real_name";
			     }
			}else{
				throw new DaoException("查询ID参数不能为空");
			}
			Query query=session.createSQLQuery(sql);
			return query.list();
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询代理商出错");
		}finally {
			if(session!=null){
				session.close();
			}
		}
		
	}
	
	@Override
	public List getOpertorsIdsTreeById(Long id) throws DaoException {
		Session session=null;
		try {
			 session = sessionFactoryDg.openSession();
			String sql = "SELECT id,real_name  FROM t_operators where role_type in ('03', '02') START WITH  ";
		    if(id!=null){
				sql+="id="+id+" CONNECT BY supri_id = PRIOR  id  order by id ";
			}else{
				throw new DaoException("查询ID参数不能为空");
			}
			Query query=session.createSQLQuery(sql);
			return query.list();
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询代理商分润规则出错");
		}finally {
			if(session!=null){
				session.close();
			}
		}
		
	}

	@Override
	public List getDirectlyAgentIdsBySuperId(Long id) throws DaoException {
		
		Session session=null;
		try {
			 session = sessionFactoryDg.openSession();
			String sql = "SELECT id,real_name  FROM t_operators where role_type in('03','02') ";
		    if(id!=null){
				sql+=" and supri_id="+id+" order by id ";
			}else{
				throw new DaoException("查询supri_id参数不能为空");
			}
			Query query=session.createSQLQuery(sql);
			return query.list();
			
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询代理商分润规则出错");
		}finally {
			if(session!=null){
				session.close();
			}
		}
		
	}
	@Override
	public List getYsbDirectlyAgentIdsBySuperId(Long id) throws DaoException {
		
		Session session=null;
		try {
			 session = sessionFactoryDg.openSession();
			String sql = "SELECT id,real_name  FROM t_operators where role_type in('03','02') ";
		    if(id!=null){
				sql+=" and (supri_id="+id+"  or id="+id+") order by id ";
			}else{
				throw new DaoException("查询supri_id参数不能为空");
			}
			Query query=session.createSQLQuery(sql);
			return query.list();
			
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询代理商分润规则出错");
		}finally {
			if(session!=null){
				session.close();
			}
		}
		
	}

	@Override
	public List<ProfitRule> queryProfitRule(String id) throws DaoException {
			List<ProfitRule> profitRuleList=new LinkedList<ProfitRule>();
			Session session=null;
			try {
				 session = sessionFactoryDg.openSession();
				StringBuffer sb=new StringBuffer("from ProfitRule p where p.agentId = ? order by p.mccTypeCode");
				
				Query query=session.createQuery(sb.toString());
				query.setString(0, id);

				profitRuleList=query.list();
			}catch (Exception e) {
				logger.debug(e.getMessage(), e);
				throw new DaoException("查询代理商分润规则出错");
			}finally {
				if(session!=null){
					session.close();
				}
			}
			return profitRuleList;
		
	}

	@Transactional(readOnly=true)
	public Operators getOpertorsByAccountNo(String accountNo)
			throws DaoException {
		Session session=null;
		Operators result = null;
		try {
			 session = sessionFactoryDg.openSession();
			Query query = session.createQuery("from Operators m where m.accountNo = :accountNo");
			query.setString("accountNo", accountNo);
			result = (Operators) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询Operators不唯一", nure);
			throw new DaoException("查询代理商不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}finally {
			if(session!=null){
				session.close();
			}
		}
		return result;

	}
	@Transactional(readOnly=true)
	public Operators getDlpOpertorsByAccountNo(String accountNo)
			throws DaoException {
		Session session=null;
		Operators result = null;
		try {
			 session = sessionFactoryDg.openSession();
			Query query = session.createQuery("from Operators m where m.groupCd='2' and m.roleType<>'15' and m.accountNo = :accountNo");
			query.setString("accountNo", accountNo);
			result = (Operators) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询Operators不唯一", nure);
			throw new DaoException("查询代理商不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}finally {
			if(session!=null){
				session.close();
			}
		}
		return result;

	}

	@Transactional(readOnly=true)
	public Object loadById(long id, Class c) throws DaoException {
		Session session=null;
		Object obj=null;
		try {
			 session = sessionFactoryDg.openSession();
			obj = session.get(c, id);
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询数据出错", e);
		}finally {
			if(session!=null){
				session.close();
			}
		}
		return obj;
	}

	@Override
	public List getDirectlyAgentIdsTreeByRealId(Long id) throws DaoException {
		Session session=null;
		try {
			 session = sessionFactoryDg.openSession();
			 String sql="";
			
			 if(id!=null){
			  sql= "select to_char(o.id) id,o.real_name from t_operators o"
					+" where o.role_type in ('03','02') and exists (select 1"
					+" from t_oper_role r  where r.operid ="+ id
					+" and o.oneagentid = r.agentid)";
			 }else{
				throw new DaoException("查询ID参数不能为空");
			 }
			 Query query=session.createSQLQuery(sql);
			 return query.list();
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询销售下面代理商出错");
		}finally {
			if(session!=null){
				session.close();
			}
		}
	}
	//查询受理市场人员
	@Override
	public List getSLOpIdsTreeBySuperId(Operators op) throws DaoException {
		Session session=null;
		try {
			 session = sessionFactoryDg.openSession();
			 String sql="";
			
			 if(op!=null){
				 if(op.getRoleType().equals("18")){
					 sql ="select  to_char(o.id) id,o.real_name from t_operators o where o.group_cd='2' and o.role_type ='16' ";
				 }else{
					 sql ="select  to_char(o.id) id,o.real_name from t_operators o where o.group_cd='2' and o.role_type ='16' and o.oneagentid="+op.getOneagentid();  
				 }
			  }else{
				throw new DaoException("只查询受理市场人员");
			 }
			 Query query=session.createSQLQuery(sql);
			 return query.list();
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询受理市场人员出错");
		}finally {
			if(session!=null){
				session.close();
			}
		}
	}

}
